package com.webwork.mood.util;

import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

/**
 * 单表数据库SQL
 *
 */
public final class DBSimpleSQL {

	/**
	 * select @param show1, @param show2 from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @param show
	 * @return
	 */
	public static String select(String table, ColumnLimit limit, String ... show) {
		StringBuffer sql = new StringBuffer("select ");
		for (String s : show) {
			sql.append(s + ", ");
		}
		sql.setLength(sql.length() - 2);
		sql.append(" from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}

	/**
	 * select distinct(@param distinct) from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @param distinct
	 * @return
	 */
	public static String selectDistince(String table, ColumnLimit limit, String... distinct) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("distinct ");
		for(int i = 0;i<distinct.length-1;i++){
		  sql.append(distinct[i]+" , ");
		}
		sql.append(distinct[distinct.length-1]);
		sql.append(" from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}
   
	/**
	 * 把所有的数据去重输出（多个判断字段）
	 * @param table
	 * @param distinct
	 * @return
	 */
	public static String selectDistince(String table, String... distinct) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("distinct ");
		for(int i = 0;i<distinct.length-1;i++){
		  sql.append(distinct[i]+" , ");
		}
		sql.append(distinct[distinct.length-1]);
		sql.append(" from ");
		sql.append(table.trim());
		return sql.toString();
	}
	/**
	 * ??
	 */
	public static String selectDistince(String table, ColumnLimit limit, String distinct1, String distinct2) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("distinct(");
		sql.append(distinct1);
		sql.append("),");
		sql.append(distinct2);
		sql.append(" from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}
	
	/**
	 * 别名<br>
	 * select map.value as map.key from @param table where @param limit1 and @param limit2<br>
	 * limit可别名也可列名
	 * @param table
	 * @param limit
	 * @param map
	 * @return
	 */
	public static String selectAilas(String table, ColumnLimit limit, Map<String, String> map) {
		StringBuffer sql = new StringBuffer("select ");
		for (Object key : map.keySet()) {
			sql.append(map.get(key));
			sql.append(" as ");
			sql.append(key);
			sql.append(", ");
		}
		sql.setLength(sql.length() - 2);
		sql.append(" from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}
	
	
	/**
	 * select * from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}

	/**
	 * select * from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @param colmn
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, String ... colmn) {
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		sql.append(" group by ");
		for (String c : colmn) {
			sql.append(c + ", ");
		}
		sql.setLength(sql.length() - 2);
		return sql.toString();
	}

	/**
	 * 
	 * 表名 table
	 * 列名 limit
	 * 页数 currPage
	 * 行数 pageSize
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, int currPage, int pageSize) {
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");

		if (pageSize > 0 && currPage > 0)
			sql.append(" LIMIT " + (currPage - 1) * pageSize + ", " + pageSize);
		return sql.toString();
	}
	
	/**
	 * 
	 * 表名 table
	 * 列名 limit
	 * 页数 currPage
	 * 行数 pageSize
	 * 唯一 colmn
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, int currPage, int pageSize, String ... colmn) {
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item))
				sql.append("?");
			sql.append(" and ");
		}
		sql.append("1=1");
		sql.append(" group by ");
		for (String c : colmn) {
			sql.append(c + ", ");
		}
		sql.setLength(sql.length() - 2);
		if (pageSize > 0 && currPage > 0)
			sql.append(" LIMIT " + (currPage - 1) * pageSize + ", " + pageSize);
		return sql.toString();
	}

	/**
	 * select count(*) from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String count(String table, ColumnLimit limit) {
		return DBSimpleSQL.count(table, limit, "");
	}

	/**
	 * select count(distinct(*)) from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @param colmn
	 * @return
	 */
	public static String count(String table, ColumnLimit limit, String ... colmn) {
		StringBuffer sql;
		String str = "";
		for (int i = 0; i < colmn.length; i++) {
			if (colmn.length > 0 && i < colmn.length - 1) {
				str += colmn[i] + ",";
			} else if (colmn.length > 0 && i == colmn.length - 1) {
				str += colmn[i];
			}
		}
		if (StrManager.isEmpty(str)) {
			sql = new StringBuffer("select count(*) from ");					//计算结果的数目
		} else {
			sql = new StringBuffer("select count(DISTINCT " + str + ") from ");	//计算非重复结果的数目
		}
		sql.append(table.trim());
		sql.append(" where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item)) {
				sql.append("?");
			}
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}

	/**
	 * insert into @param table values (?, ?, ...)
	 * @param table
	 * @param columnSize
	 * @return
	 */
	public static String insert(String table, int columnSize) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table.trim());
		sql.append(" values (");
		for (int i = 0; i < columnSize - 1; i++) {
			sql.append("?, ");
		}
		sql.append("?)");
		return sql.toString();
	}

	/**
	 * delete from @param table where @param limit1 and @param limit2
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String del(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table.trim() + " where ");
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item)) {
				sql.append("?");
			}
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}

	public static String update(String table, LinkedHashMap<String, String> dbColumns, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("update ");
		sql.append(table.trim());
		sql.append(" set ");
		Iterator<Entry<String, String>> lit = dbColumns.entrySet().iterator();
		int i = 0;
		while (lit.hasNext()) {
			Map.Entry<String, String> colmn = (Map.Entry<String, String>) lit.next();
			if (i < dbColumns.size() - 1) {
				sql.append(colmn.getValue() + " = ?, ");
			} else {
				sql.append(colmn.getValue() + " = ? where ");
			}
			i++;
		}
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StrManager.isEmpty(item)) {
				sql.append("?");
			}
			sql.append(" and ");
		}
		sql.append("1=1");
		return sql.toString();
	}
	
}

